USE [IUDICIUM]
GO

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tbl_PersonaNatural]'))
ALTER FULLTEXT INDEX ON [dbo].[tbl_PersonaNatural] DISABLE
GO

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tbl_PersonaNatural]'))
DROP FULLTEXT INDEX ON [dbo].[tbl_PersonaNatural]

GO

IF  EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'CatPersonaNatural')
DROP FULLTEXT CATALOG [CatPersonaNatural]
GO

CREATE FULLTEXT CATALOG [CatPersonaNatural]WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]

GO

CREATE FULLTEXT INDEX ON [dbo].[tbl_PersonaNatural]
 ( 
  [nombre] Language 1033 
 ) 
  KEY INDEX PK_tbl_PersonaNatural
      ON [CatPersonaNatural]; 
GO

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tbl_PersonaJuridica]'))
ALTER FULLTEXT INDEX ON [dbo].[tbl_PersonaJuridica] DISABLE
GO

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tbl_PersonaJuridica]'))
DROP FULLTEXT INDEX ON [dbo].[tbl_PersonaJuridica]

GO

IF  EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'CatPersonaJuridica')
DROP FULLTEXT CATALOG [CatPersonaJuridica]
GO

CREATE FULLTEXT CATALOG [CatPersonaJuridica] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]

GO


CREATE FULLTEXT INDEX ON [dbo].[tbl_PersonaJuridica]
 ( 
  [razonSocial] Language 1033 
 ) 
  KEY INDEX PK_tbl_PersonaJuridica
      ON [CatPersonaJuridica]; 
GO


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tbl_Archivo_temp]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[tbl_Archivo] DROP CONSTRAINT [DF_tbl_Archivo_temp]
END

GO


/****** Object:  Table [dbo].[tbl_RelacionArchivo]    Script Date: 12/19/2012 01:45:39 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_RelacionArchivo]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_RelacionArchivo]
GO

/****** Object:  Table [dbo].[tbl_TipoRelacionArchivo]    Script Date: 12/19/2012 01:45:39 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_TipoRelacionArchivo]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_TipoRelacionArchivo]
GO

/****** Object:  Table [dbo].[tbl_Archivo]    Script Date: 12/19/2012 01:45:39 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Archivo]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_Archivo]
GO


/****** Object:  Table [dbo].[tbl_RelacionArchivo]    Script Date: 12/19/2012 01:45:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_RelacionArchivo](
	[relacionArchivoId] [int] IDENTITY(1,1) NOT NULL,
	[archivoId] [int] NOT NULL,
	[objetoId] [int] NOT NULL,
	[tipoRelacionArchivoId] [varchar](50) NOT NULL,
	[servicioId] [int] NOT NULL,
 CONSTRAINT [PK_tbl_RelacionArchivo] PRIMARY KEY CLUSTERED 
(
	[relacionArchivoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[tbl_TipoRelacionArchivo]    Script Date: 12/19/2012 01:45:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_TipoRelacionArchivo](
	[tipoId] [varchar](50) NOT NULL,
	[nombreRelacion] [varchar](100) NOT NULL,
 CONSTRAINT [PK_tbl_TipoRelacionArchivo] PRIMARY KEY CLUSTERED 
(
	[tipoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[tbl_Archivo]    Script Date: 12/19/2012 01:45:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_Archivo](
	[archivoId] [int] IDENTITY(1,1) NOT NULL,
	[nombre] [varchar](500) NOT NULL,
	[extension] [varchar](10) NULL,
	[fechaCreacion] [datetime] NOT NULL,
	[rutaAlmacenamiento] [varchar](1000) NOT NULL,
	[temp] [bit] NOT NULL,
 CONSTRAINT [PK_tbl_Archivo] PRIMARY KEY CLUSTERED 
(
	[archivoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_Archivo] ADD  CONSTRAINT [DF_tbl_Archivo_temp]  DEFAULT ((1)) FOR [temp]
GO


ALTER TABLE [dbo].[tbl_RelacionArchivo]
	ADD FOREIGN KEY (archivoId) REFERENCES [dbo].[tbl_Archivo](archivoId)
GO

ALTER TABLE [dbo].[tbl_RelacionArchivo]
	ADD FOREIGN KEY (tipoRelacionArchivoId) REFERENCES [dbo].[tbl_TipoRelacionArchivo](tipoId)
GO

ALTER TABLE [dbo].[tbl_RelacionArchivo]
	ADD FOREIGN KEY (servicioId) REFERENCES [dbo].[tbl_Servicio](servicioId)
GO


/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTramiteUtilidad]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetTramiteUtilidad]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_GetTramiteUtilidad]
GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_GetPersonaNaturalBySearch]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PERSONA_GetPersonaNaturalBySearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_PERSONA_GetPersonaNaturalBySearch]
GO

/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTramiteAlert]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetTramiteAlert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_GetTramiteAlert]
GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_GetArchivosByRelacion]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FILE_GetArchivosByRelacion]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_FILE_GetArchivosByRelacion]
GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_InsertRelacionArchivo]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FILE_InsertRelacionArchivo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_FILE_InsertRelacionArchivo]
GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_GetArchivosByArchivoIds]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FILE_GetArchivosByArchivoIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_FILE_GetArchivosByArchivoIds]
GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_EliminarArchivoById]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FILE_EliminarArchivoById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_FILE_EliminarArchivoById]
GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_InsertarArchivo]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FILE_InsertarArchivo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_FILE_InsertarArchivo]
GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_GetArchivo]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FILE_GetArchivo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_FILE_GetArchivo]
GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_SearchAdministrativosForResponsables]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PERSONA_SearchAdministrativosForResponsables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_PERSONA_SearchAdministrativosForResponsables]
GO

/****** Object:  StoredProcedure [dbo].[usp_USER_GetUsuariosBySearch]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_USER_GetUsuariosBySearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_USER_GetUsuariosBySearch]
GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_GetAdministrativoBySearch]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PERSONA_GetAdministrativoBySearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_PERSONA_GetAdministrativoBySearch]
GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_GetPersonaNaturalBySearchConExcepcion]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PERSONA_GetPersonaNaturalBySearchConExcepcion]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_PERSONA_GetPersonaNaturalBySearchConExcepcion]
GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_GetPersonaJuridicaBySearch]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PERSONA_GetPersonaJuridicaBySearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_PERSONA_GetPersonaJuridicaBySearch]
GO

/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTramiteBySearch]    Script Date: 12/19/2012 01:43:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SERVICIO_GetTramiteBySearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SERVICIO_GetTramiteBySearch]
GO


/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTramiteUtilidad]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jimmy Daniel Montero
-- Create date: 28/01/2012
-- Description: Obtiene las utilidades de los Tramites.
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_GetTramiteUtilidad]
	@bitTienePermiso	BIT,
	@intUsuarioId		INT,
	@dateFechaInicio	DATE,
	@dateFechaFin		DATE,
	@totalIngeso		DECIMAl(10,2) OUTPUT,
	@totalGasto			DECIMAl(10,2) OUTPUT,
	@totalUtilidad		DECIMAl(10,2) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	
	IF @dateFechaInicio IS NULL
	BEGIN
		SELECT @dateFechaInicio = MIN([fechaInicio])
		FROM [dbo].[tbl_Servicio]
	END
	
	IF @dateFechaFin IS NULL
	BEGIN
		SELECT @dateFechaFin = GETDATE()
		FROM [dbo].[tbl_Servicio]
	END
	
	DECLARE @tblServicios AS TABLE([id] INT)
	IF @bitTienePermiso = 0
	BEGIN
	
		INSERT INTO @tblServicios ([id])
			SELECT [servicioId] FROM [dbo].[tbl_Responsable] WHERE [administrativoId] = @intUsuarioId AND [estado] = 'Activo'
	END
	
	DECLARE @tblResult AS TABLE(
		[servicioId] INT,
		[fechaInicio] DATE,
		[codigoServicio] VARCHAR(50),
		[tipotramite] VARCHAR(100),
		[categoria] VARCHAR(100),
		[responsables] VARCHAR(8000),
		[involucrados] VARCHAR(8000),
		[ingresos] DECIMAL(10,2),
		[egresos] DECIMAL(10,2),
		[utilidad] DECIMAL(10,2)
	)
	
	INSERT INTO @tblResult
		SELECT 
				s.[servicioId],
				CAST(s.fechaInicio AS DATE) AS [fechaInicio],
				s.[codigoServicio],
				tp.[descripcion] AS [tipotramite],
				ct.[descripcion] AS [categoria],
				ISNULL([dbo].[uf_GetResponsablesList](s.[servicioId]),'') AS [responsables],
				ISNULL([dbo].[uf_GetInvolucradosList](s.[servicioId]),'') AS [involucrados],
				ISNULL([dbo].[uf_GetIngresos](s.[servicioId]),0) AS [ingresos],
				ISNULL([dbo].[uf_GetEgresos](s.[servicioId]),0) AS [egresos],
				((ISNULL([dbo].[uf_GetIngresos](s.[servicioId]),0)) - (ISNULL([dbo].[uf_GetEgresos](s.[servicioId]),0))) AS [utilidad]
				
				
		FROM
			[dbo].[tbl_Tramite] t,
			[dbo].[tbl_Servicio] s,
			[dbo].[tbl_Transaccion] tr,
			[dbo].[tbl_CategoriaTramite] ct,
			[dbo].[tbl_TipoTramite] tp
			
		WHERE
			s.[servicioId] = t.[tramiteId]
			AND	s.[servicioId]=tr.[servicioId]
			AND ct.[categoriaTramiteId] = tp.[categoriaTramiteId]
			AND tp.[tipoTramiteId] = t.[tipoTramiteId]
			AND S.[tipoServicio] = 'Tramite'
			AND S.[fechaInicio] BETWEEN @dateFechaInicio AND @dateFechaFin
		GROUP BY 
			s.servicioId,
			s.fechaInicio,
			s.codigoServicio,
			tp.descripcion,
			ct.descripcion
			
	SELECT 
		@totalIngeso = SUM([ingresos]),
		@totalGasto = SUM([egresos]),
		@totalUtilidad = SUM([utilidad])
	FROM @tblResult 
	
	SELECT
		[servicioId],
		[fechaInicio],
		[codigoServicio],
		[tipotramite],
		[categoria],
		[responsables],
		[involucrados],
		[ingresos],
		[egresos],
		[utilidad]
	FROM @tblResult
		
END

GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_GetPersonaNaturalBySearch]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 17/08/2011
-- Description:	Obtiene una lista de personas segun parametro de busqueda
-- =============================================
CREATE PROCEDURE [dbo].[usp_PERSONA_GetPersonaNaturalBySearch]
	@varParametroBusqueda nvarchar(4000),
	@bitIncluirAdmin	  bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	IF(@varParametroBusqueda IS NULL OR @varParametroBusqueda = '')
		SET @varParametroBusqueda = ' 1 = 1 '
	
	IF @bitIncluirAdmin <> 1
		SET @varParametroBusqueda = @varParametroBusqueda + ' AND [personaNaturalId] <> 1'
	
	DECLARE @query VARCHAR(5000)
	
	SET @query = '
		SELECT	[personaNaturalId]
				,[nombre]
				,[cedula]
				,[sexo]
				,[expedicionCedula]
		FROM	[dbo].[tbl_PersonaNatural]
		WHERE	' + @varParametroBusqueda
		
	PRINT @query
	
	EXEC(@query)
    
END

GO

/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTramiteAlert]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 02/01/2012
-- Description:	recupera registros de tramites con fecha de finalizacion estimada.
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_GetTramiteAlert]
	@bitTienePermiso	BIT,
	@intUsuarioId		INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @tblServicios AS TABLE([id] INT)
	IF @bitTienePermiso = 0
	BEGIN
	
		INSERT INTO @tblServicios ([id])
			SELECT [servicioId] FROM [dbo].[tbl_Responsable] WHERE [administrativoId] = @intUsuarioId AND [estado] = 'Activo'
	END

    SELECT 
			t.[tramiteId],
			CAST(s.fechaEntregaEstimada AS DATE) AS [fecha],
			s.[codigoServicio],
			tp.[descripcion] AS [tipotramite],
			ct.[descripcion] AS [categoria],
			ISNULL([dbo].[uf_GetResponsablesList](s.[servicioId]),'') AS [responsables],
			ISNULL([dbo].[uf_GetInvolucradosList](s.[servicioId]),'') AS [involucrados],
			DATEDIFF(DAY,s.[fechaEntregaEstimada],GETDATE()) AS [diasA]
	FROM 
			[dbo].[tbl_Tramite] t,
			[dbo].[tbl_Servicio] s,
			[dbo].[tbl_CategoriaTramite] ct,
			[dbo].[tbl_TipoTramite] tp
	
	WHERE
		s.[servicioId] = t.[tramiteId]
		AND	
		ct.[categoriaTramiteId] = tp.[categoriaTramiteId]
		AND
		tp.[tipoTramiteId] = t.[tipoTramiteId]
		
		AND
		S.[tipoServicio] = 'Tramite'
		AND
		s.[estado] = 'En Proceso'
		AND
		CAST(s.[fechaEntregaEstimada] AS DATE) <= CAST(GETDATE() AS DATE) 
		AND
		(@bitTienePermiso = 1 OR
		(@bitTienePermiso = 0 
		AND s.[servicioId] IN (SELECT [id] FROM @tblServicios)))
		
END

GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_GetArchivosByRelacion]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 22/11/2012
-- Description:	Obtiene la informacion de la relacion de un archivo con un objeto de servicio
-- =============================================
CREATE PROCEDURE [dbo].[usp_FILE_GetArchivosByRelacion]
	@intObjetoId INT,
	@varTipoRelacion VARCHAR(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    
    SELECT [relacionArchivoId]
	  ,a.[nombre]
      ,r.[archivoId]
      ,[objetoId]
      ,t.[tipoId]
      ,[servicioId]
	FROM [dbo].[tbl_RelacionArchivo] r
		JOIN [dbo].[tbl_TipoRelacionArchivo] t ON t.[tipoId] = r.[tipoRelacionArchivoId]
		JOIN [dbo].[tbl_Archivo] a ON a.[archivoId] = r.[archivoId]
	WHERE t.[nombreRelacion] = @varTipoRelacion
		AND r.[objetoId] = @intObjetoId

    
END

GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_InsertRelacionArchivo]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 05/12/2012
-- Description:	Inserta una relacion de archivos con un objeto
-- =============================================
CREATE PROCEDURE [dbo].[usp_FILE_InsertRelacionArchivo]
	@varArchivoIds		VARCHAR(8000),
	@intObjetoId		INT,
	@varTipoRelacion	VARCHAR(50),
	@intServicioId		INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	BEGIN TRANSACTION
	
	BEGIN TRY

		INSERT INTO [dbo].[tbl_RelacionArchivo]
			   ([archivoId]
			   ,[objetoId]
			   ,[tipoRelacionArchivoId]
			   ,[servicioId])
		 SELECT CAST([splitvalue] AS INT),
				@intObjetoId,
				@varTipoRelacion,
				@intServicioId
		 FROM [dbo].[uf_SplitString](@varArchivoIds, ',')

		COMMIT TRANSACTION		
			
			
	END TRY
	BEGIN CATCH
		
		ROLLBACK TRANSACTION
		
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT; 
		SELECT  @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(); 
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   ); 
		
	END CATCH


END

GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_GetArchivosByArchivoIds]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 27/11/2012
-- Description:	Obtiene los archivos a partir de una lista de ids
-- =============================================
CREATE PROCEDURE [dbo].[usp_FILE_GetArchivosByArchivoIds]
	@varArchivoIds AS VARCHAR(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT [archivoId]
      ,[nombre]
	FROM [dbo].[tbl_Archivo]
	WHERE [archivoId] IN (
		SELECT CAST([splitvalue] AS INT)
		FROM [dbo].[uf_SplitString](@varArchivoIds,','))
			
  
END

GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_EliminarArchivoById]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 22/11/2012
-- Description:	Elimina un archivo de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_FILE_EliminarArchivoById]
	@intArchivoId INT,
	@varRutaAlmacenamiento VARCHAR(1000) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRANSACTION
	
	BEGIN TRY
	
		DELETE FROM [dbo].[tbl_RelacionArchivo]
		WHERE [archivoId] = @intArchivoId
		
		SELECT @varRutaAlmacenamiento = [rutaAlmacenamiento]
		FROM [dbo].[tbl_Archivo]
		WHERE [archivoId] = @intArchivoId
		
		DELETE FROM [dbo].[tbl_Archivo]
		WHERE [archivoId] = @intArchivoId
		
		COMMIT TRANSACTION		
			
			
	END TRY
	BEGIN CATCH
		
		ROLLBACK TRANSACTION
		
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT; 
		SELECT  @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(); 
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   ); 
		
	END CATCH
END

GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_InsertarArchivo]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 21/11/2012
-- Description:	Inserta un archivo en la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_FILE_InsertarArchivo]
	@intArchivoId			INT OUTPUT,
	@varNombre				VARCHAR(500),
	@varExtension			VARCHAR(10),
	@varFechaCreacion		DATETIME,
	@varRutaAlmacenamiento	VARCHAR(1000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRANSACTION
	
	BEGIN TRY
	
		INSERT INTO [dbo].[tbl_Archivo]
			   ([nombre]
			   ,[extension]
			   ,[fechaCreacion]
			   ,[rutaAlmacenamiento]
			   ,[temp])
		 VALUES
			   (@varNombre
			   ,@varExtension
			   ,@varFechaCreacion
			   ,@varRutaAlmacenamiento
			   ,1)
			   
		SET @intArchivoId = SCOPE_IDENTITY()
		
		COMMIT TRANSACTION		
			
			
	END TRY
	BEGIN CATCH
		
		ROLLBACK TRANSACTION
		
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT; 
		SELECT  @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(); 
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   ); 
		
	END CATCH

END

GO

/****** Object:  StoredProcedure [dbo].[usp_FILE_GetArchivo]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 21/11/2012
-- Description:	Obtiene la informacion de un archivo
-- =============================================
CREATE PROCEDURE [dbo].[usp_FILE_GetArchivo]
	@intArchivoId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT [archivoId]
      ,[nombre]
      ,[extension]
      ,[fechaCreacion]
      ,[rutaAlmacenamiento]
      ,[temp]
	FROM [dbo].[tbl_Archivo]
	WHERE [archivoId] = @intArchivoId


END

GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_SearchAdministrativosForResponsables]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 09/01/2012
-- Description:	Realiza busqueda de administrativos
-- =============================================
CREATE PROCEDURE [dbo].[usp_PERSONA_SearchAdministrativosForResponsables]
	@varSearchTerm	VARCHAR(8000),
	@varIds			VARCHAR(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    IF @varSearchTerm IS NULL
		SET @varSearchTerm = ' 1 = 1 '
		
	IF @varIds IS NULL OR @varIds = ''
		SET @varIds = '0'
	
	DECLARE @query VARCHAR(5000)
	
	SET @query = '		
	SELECT	pn.[personaNaturalId] AS [personaId],
			pn.[nombre]
    FROM	[dbo].[tbl_PersonaNatural] pn
			JOIN [dbo].[tbl_Administrativo] a ON a.[administrativoId] = pn.[personaNaturalId]
	WHERE	(' + @varSearchTerm + ')
			AND pn.[personaNaturalId] NOT IN (' + @varIds + ')'
	--pn.[nombre] LIKE '%' + @varSearchTerm + '%'
	--OR pn.[cedula]  LIKE '%' + @varSearchTerm + '%'
	
	PRINT @query
	
	EXEC(@query)
END

GO

/****** Object:  StoredProcedure [dbo].[usp_USER_GetUsuariosBySearch]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 04/12/2011
-- Description:	Obtiene una lista de usuarios a traves de un criterio de busqueda
-- =============================================
CREATE PROCEDURE [dbo].[usp_USER_GetUsuariosBySearch]
	@varSearchTerm	VARCHAR(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	IF(@varSearchTerm IS NULL)
		SET @varSearchTerm = ' 1 = 1 '

	DECLARE @query VARCHAR(5000)
	
	SET @query = '
		SELECT	[usuarioId]
				,[nombreUsuario]
				,p.[nombre]
				,usr.[estado]
				,rol.[nombre] AS [nombreRol]
		FROM	[dbo].[tbl_Usuario] usr
				JOIN [dbo].[tbl_PersonaNatural] p ON [personaNaturalId] = [usuarioId]
				JOIN [dbo].[tbl_Rol] rol ON rol.[rolId] = usr.[rolId]
		WHERE	' + @varSearchTerm
		--p.[nombre] LIKE '%' + @varSearchTerm + '%'
		--		OR p.[expedicionCedula] LIKE '%' + @varSearchTerm + '%'
		--		OR usr.[nombreUsuario]  LIKE '%' + @varSearchTerm + '%'
		--		OR rol.[nombre] LIKE '%' + @varSearchTerm + '%''
		
	PRINT @query
	
	EXEC(@query)
  
END

GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_GetAdministrativoBySearch]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 06/09/2011
-- Description:	obtiene un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_PERSONA_GetAdministrativoBySearch]
	@varParametroBusqueda nvarchar(4000)
AS
BEGIN
	SET NOCOUNT ON;
	IF(@varParametroBusqueda IS NULL)
		SET @varParametroBusqueda = ' 1 = 1 '

	DECLARE @query VARCHAR(5000)
	
	SET @query = '
		SELECT	ad.[administrativoId]
				,ad.[fechaIngreso]
				,ad.[cargoId]
				,pn.[nombre]
				,c.[descripcion] AS [nombreCargo]
		FROM	[dbo].[tbl_Administrativo] ad
				JOIN 
				[dbo].[tbl_PersonaNatural] pn ON ad.[administrativoId] = pn.[personaNaturalId]
				JOIN
				[dbo].[tbl_Cargo] c ON c.[cargoId] = ad.[cargoId]
		WHERE   ' + @varParametroBusqueda
		/*pn.[nombre] LIKE '%' + @varParametroBusqueda + '%' 
				OR
				c.[descripcion] LIKE '%' + @varParametroBusqueda + '%'*/
				
	PRINT @query

	EXEC(@query)
	
END

GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_GetPersonaNaturalBySearchConExcepcion]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 17/08/2011
-- Description:	Obtiene una lista de personas segun parametro de busqueda
-- =============================================
CREATE PROCEDURE [dbo].[usp_PERSONA_GetPersonaNaturalBySearchConExcepcion]
	@varParametroBusqueda	nvarchar(4000),
	@varPersonaIds			varchar(4000),
	@varNombreTabla			varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @query VARCHAR(5000)
	
	
	IF(@varParametroBusqueda IS NULL)
		SET @varParametroBusqueda = ' 1 = 1 '
	
	IF(@varPersonaIds IS NULL)
		SET @varPersonaIds = '0' 
	
	SET @query = '
	
	DECLARE @tblTemp AS TABLE ([id] INT)
	
	IF(NOT(''' + @varNombreTabla +''' = ''''))
	BEGIN
	
		IF(''' + @varNombreTabla +''' = ''Usuario'')
		BEGIN
			
			INSERT INTO @tblTemp 
				SELECT  [usuarioId] FROM [dbo].[tbl_Usuario]
		
		END
		
		IF(''' + @varNombreTabla +''' = ''Administrativo'')
		BEGIN
			
			INSERT INTO @tblTemp 
				SELECT  [administrativoId] FROM [dbo].[tbl_Administrativo]
		
		END
		
	END
	

    SELECT	[personaNaturalId]
			,[nombre]
			,[cedula]
			,[sexo]
			,[expedicionCedula]
	FROM	[dbo].[tbl_PersonaNatural]
	WHERE	(' + @varParametroBusqueda + ') AND
			[personaNaturalId] NOT IN (' + @varPersonaIds + ') AND
			[personaNaturalId] NOT IN (SELECT [id] FROM @tblTemp)'
   -- ([nombre] like '%' + @varParametroBusqueda + '%' OR
			--[cedula] like '%' + @varParametroBusqueda + '%')
	PRINT @query

	EXEC(@query)
	
END

GO

/****** Object:  StoredProcedure [dbo].[usp_PERSONA_GetPersonaJuridicaBySearch]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 05/09/2011
-- Description:	obtiene una persona juridica de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_PERSONA_GetPersonaJuridicaBySearch]
	@varParametroBusqueda nvarchar(4000)
AS
BEGIN
	SET NOCOUNT ON;
	
	IF(@varParametroBusqueda IS NULL OR @varParametroBusqueda = '')
		SET @varParametroBusqueda = ''
	DECLARE @query VARCHAR(5000)
	
	SET @query = '
    SELECT	[personaJuridicaId]
			,[razonSocial]
			,[nit]
	FROM	[dbo].[tbl_PersonaJuridica]
	WHERE	' + @varParametroBusqueda
	--[razonSocial] LIKE '%' + @varParametroBusqueda + '%' OR
	--		[nit] LIKE '%' + @varParametroBusqueda + '%'
	
	PRINT @query
	
	EXEC(@query)
    
END

GO

/****** Object:  StoredProcedure [dbo].[usp_SERVICIO_GetTramiteBySearch]    Script Date: 12/19/2012 01:43:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Noemi Ingrid Perez Benitez
-- Create date: 08/11/2011
-- Description:	obtiene un registro de la base de datos
-- =============================================
CREATE PROCEDURE [dbo].[usp_SERVICIO_GetTramiteBySearch]
	@varParametroBusqueda	NVARCHAR(4000),
	@varEstados				VARCHAR(150),
	@bitTienePermiso		BIT,
	@intUsuarioId			INT
AS
BEGIN

	SET NOCOUNT ON;
    IF(@varParametroBusqueda IS NULL OR @varParametroBusqueda = '')
		SET @varParametroBusqueda = ''
		
	DECLARE @tblEstados AS TABLE([estado] VARCHAR(15))
	INSERT INTO @tblEstados
		SELECT [splitvalue]
		FROM [dbo].[uf_SplitString](@varEstados, ',')
		
	DECLARE @tblServicios AS TABLE([id] INT)
	IF @bitTienePermiso = 0
	BEGIN
	
		INSERT INTO @tblServicios ([id])
			SELECT [servicioId] FROM [dbo].[tbl_Responsable] WHERE [administrativoId] = @intUsuarioId AND [estado] = 'Activo'
	END
	
	SELECT DISTINCT
			[tramiteId]
			,[codigoServicio]
			,t.[tipoTramiteId] 
			,s.[fechaInicio]
			,[fechaEntregaEstimada]	
			,ISNULL(s.[fechaFin],'') AS [fechaFin]
			,[observacion]
			,s.[estado]
			,ISNULL([fechaEntrega],'') AS [fechaEntrega]
			,ISNULL([entregadoA],'') AS [entregadoA]
			,ISNULL([observacionEntrega],'') AS [observacionEntrega]
			,ISNULL([dbo].[uf_GetResponsablesList](s.[servicioId]),'') AS [responsables]
			,ISNULL([dbo].[uf_GetInvolucradosList](s.[servicioId]),'') AS [involucrados]
			,tp.[descripcion]AS tipo
			,ct.[descripcion]AS categoria
	FROM	[dbo].[tbl_Tramite] t
		JOIN [dbo].[tbl_Servicio] s ON s.[servicioId] = t.[tramiteId] AND s.[tipoServicio] = 'Tramite'
		JOIN [dbo].[tbl_TipoTramite] tp ON tp.[tipoTramiteId] = t.[tipoTramiteId]
		JOIN [dbo].[tbl_CategoriaTramite] ct ON ct.[categoriaTramiteId] = tp.[categoriaTramiteId] 
		JOIN [dbo].[tbl_Involucrado] i ON i.[servicioId] = s.[servicioId]
		JOIN [dbo].[uv_Involucrados] inv ON inv.[involucradoId] = i.[involucradoId]
		JOIN [dbo].[tbl_Responsable] r ON  r.[servicioId] = s.[servicioId]
		JOIN [dbo].[uv_Responsables] adm ON adm.[responsableId] = r.[responsableId]
	WHERE   
			(inv.[nombre] LIKE @varParametroBusqueda + '%'
			OR adm.[nombre] LIKE @varParametroBusqueda + '%'
			OR s.[codigoServicio] LIKE '%' + @varParametroBusqueda + '%'  
			OR tp.[descripcion] LIKE @varParametroBusqueda + '%')
			AND s.[estado] IN (SELECT [estado] FROM @tblEstados)
			AND (@bitTienePermiso = 1 OR
				(@bitTienePermiso = 0 AND s.[servicioId] IN (SELECT [id] FROM @tblServicios)))
	ORDER BY [tramiteId] DESC

END


GO


DELETE FROM [dbo].[tbl_Version]
GO

INSERT INTO [dbo].[tbl_Version]
           ([versionMayor]
           ,[versionMenor]
           ,[release])
     VALUES
           (1
           ,4
           ,0)
GO

